#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into intention_dwd.dim_dwd_customer
select
id,
customer_relationship_id,
create_date_time,
update_date_time,
deleted,
name,
idcard,
birth_year,
gender,
phone,
wechat,
qq,
email,
area,
leave_school_date,
graduation_date,
bxg_student_id,
creator,
origin_type,
origin_channel,
tenant,
md_id,
'9999-99-99' as end_date,
dt as start_date

from intention_ods.ods_customer;

select * from intention_dwd.dim_dwd_customer;

insert into intention_dwd.dim_dwd_customer_appeal
select
id,
customer_relationship_first_id,
employee_id,
employee_name,
employee_department_id,
employee_tdepart_id,
appeal_status,
audit_id,
audit_name,
audit_department_id,
audit_department_name,
audit_date_time,
create_date_time,
update_date_time,
deleted,
tenant,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_customer_appeal;

insert into intention_dwd.dim_dwd_customer_clue
select
id,
create_date_time,
update_date_time,
deleted,
customer_id,
customer_relationship_id,
session_id,
sid,
"status",
"user",
create_time,
platform,
s_name,
seo_source,
seo_keywords,
ip,
referrer,
from_url,
landing_page_url,
url_title,
to_peer,
manual_time,
begin_time,
reply_msg_count,
total_msg_count,
msg_count,
comment,
finish_reason,
finish_user,
end_time,
platform_description,
browser_name,
os_info,
area,
country,
province,
city,
creator,
name,
idcard,
phone,
itcast_school_id,
itcast_school,
itcast_subject_id,
itcast_subject,
wechat,
qq,
email,
gender,
level,
origin_type,
information_way,
working_years,
technical_directions,
customer_state,
valid,
anticipat_signup_date,
clue_state,
scrm_department_id,
superior_url,
superior_source,
landing_url,
landing_source,
info_url,
info_source,
origin_channel,
course_id,
course_name,
zhuge_session_id,
is_repeat,
tenant,
activity_id,
activity_name,
follow_type,
shunt_mode_id,
shunt_employee_group_id,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_customer_clue;

select * from intention_dwd.dim_dwd_customer_clue;

insert into intention_dwd.fact_dwd_customer_relationship
select
id,
create_date_time,
update_date_time,
deleted,
customer_id,
first_id,
belonger,
belonger_name,
initial_belonger,
distribution_handler,
business_scrm_department_id,
last_visit_time,
next_visit_time,
origin_type,
itcast_school_id,
itcast_subject_id,
intention_study_type,
anticipat_signup_date,
level,
creator,
current_creator,
creator_name,
origin_channel,
comment,
first_customer_clue_id,
last_customer_clue_id,
process_state,
process_time,
payment_state,
payment_time,
signup_state,
signup_time,
notice_state,
notice_time,
lock_state,
lock_time,
itcast_clazz_id,
itcast_clazz_time,
payment_url,
payment_url_time,
ems_student_id,
delete_reason,
deleter,
deleter_name,
delete_time,
course_id,
course_name,
delete_comment,
close_state,
close_time,
appeal_id,
tenant,
total_fee,
belonged,
belonged_time,
belonger_time,
transfer,
transfer_time,
follow_type,
transfer_bxg_oa_account,
transfer_bxg_belonger_name,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_customer_relationship;

insert into intention_dwd.dim_dwd_employee
SELECT
id,
email,
real_name,
phone,
department_id,
department_name,
remote_login,
job_number,
cross_school,
last_login_date,
creator,
create_date_time,
update_date_time,
deleted,
scrm_department_id,
leave_office,
leave_office_time,
reinstated_time,
superior_leaders_id,
tdepart_id,
tenant,
ems_user_name,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_employee;

insert into intention_dwd.dim_dwd_ods_itcast_clazz
select
id,
create_date_time,
update_date_time,
deleted,
itcast_school_id,
itcast_school_name,
itcast_subject_id,
itcast_subject_name,
itcast_brand,
clazz_type_state,
clazz_type_name,
teaching_mode,
start_time,
end_time,
comment,
detail,
uncertain,
tenant,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_itcast_clazz;

insert into intention_dwd.dim_dwd_itcast_school
select
id,
create_date_time,
update_date_time,
deleted,
name,
code,
tenant,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_itcast_school;

insert into intention_dwd.dim_dwd_itcast_subject
SELECT
id,
create_date_time,
update_date_time,
deleted,
name,
code,
tenant,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_itcast_subject;

insert into intention_dwd.dim_dwd_scrm_department
select
id,
name,
parent_id,
create_date_time,
update_date_time,
deleted,
id_path,
tdepart_code,
creator,
depart_level,
depart_sign,
depart_line,
depart_sort,
disable_flag,
tenant,
'9999-99-99' as end_date,
dt as start_date
from intention_ods.ods_scrm_department;
"